-- @author lixiyong01
-- @date 2023.03.01
-- 整合对外的宽表层流量明细层（用户为登录用户即访客）

CREATE TABLE `hdp_lbg_supin_zplisting.app_zp_wb_n_flow_slot_cube`(
    `platform` int COMMENT '平台类型,1-pc，2-m，3-app， 4-小程序， 5-wap',
    `slot` string COMMENT '推荐位标识 主键',
    `request_type` string COMMENT '请求商业类型',
    `info_cate2` string COMMENT '职位二级类别',
    `info_cate3` string COMMENT '职位三级类别',
    `info_local1` string COMMENT '职位一级发布地域',

    `exp_cnt` bigint COMMENT '曝光次数',
    `click_cnt` bigint COMMENT '点击次数',
    `b_im_cnt` int COMMENT 'B发起微聊量',
    `c_im_cnt` int COMMENT 'C发起微聊量',
    `delivery_cnt` bigint COMMENT '投递数，分享场景会n次投递',
    `tel_cnt` bigint COMMENT '电话沟通次数',
    `tel_conn_cnt` bigint COMMENT '电话接通次数',
    `yx_cost` bigint COMMENT '优选扣费总金额（万分之一元）',
    `yx_cash` bigint COMMENT '优选扣费现金（万分之一元）',
    `jz_cost` bigint COMMENT '黄金展位扣费总金额（万分之一元）',
    `jz_cash` bigint COMMENT '黄金展位扣费现金（万分之一元）'
)
COMMENT '58招聘行为串联扩展表'
PARTITIONED BY (`dt` string COMMENT '日期分区')
STORED AS orc


select a.info_local1, b.areaname,
  uv, buid_cnt, info_cnt, exp_cnt, click_cnt, im_cnt2, tel_cnt2, delivery_cnt2
from (
    select info_local1,
        count(distinct ukey) as uv,
        count(distinct buid) as buid_cnt,
        count(distinct info_id) as info_cnt,
        sum(has_exposure) as exp_cnt,
        sum(has_infodetail) as click_cnt,
        sum(if(bc_type='c', has_im, 0)) as im_cnt2,
        sum(if(tel_conn_cnt>0, has_tel, 0)) as tel_cnt2,
        sum(has_delivery) as delivery_cnt2
    from hdp_lbg_supin_zplisting.app_zp_gj_n_flow
    where dt=20230223 and info_cate3 in (
        select cateid
        from hdp_58_common_defaultdb.ds_dict_cmc_category
        where statdate='20230223' and catename='送餐员' and cateids[0]='574'
    )
    group by info_local1
) a
inner join (
    select areaid,areaname,depth,areaids
    from hdp_58_common_defaultdb.ds_dict_cmc_displocal
    where statdate='20230223' and depth=0
) b on a.info_local1=b.areaid



left join
    hdp_58_common_defaultdb.ds_dict_cmc_category b
    on a.info_local1 = b.local1

left join
    hdp_58_common_defaultdb.ds_dict_cmc_displocal c
